/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package refactoring;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import java.util.Vector;

/**
 *
 * @author kvsevolo
 */
public class TableManager {
    public static class TableNames {
        public static final String TableName = "HYMNS_TABLE";
        public static final String TableFirstCharName = "FIRST_CHAR_TABLE";
        public static final String TableMainCategoryName = "MAIN_CATEGORY_TABLE";
        public static final String TableSubCategoryName = "SUB_CATEGORY_TABLE";
        public static final String TableFirstLineName = "FIRST_LINE_TABLE";
    }
    
    static TableManager instance = null;
    static final String TABLE_SEPARATOR = "==";
    Connection dbConnector;
    static String melodyTableFile;    
    
    static int themeCount = 0;
    static class ThemeNameID {
        
        protected int themeID;
        protected String themeName;
        
        public ThemeNameID(int id, String str) {
            themeID = id;
            themeName = str;
        }
    }
    
    static class SubThemeNameID extends Object{
        
        protected String mainThemeID;
        protected String subTheme;
        protected String firstLines;
        protected int hashCode;
        
        public SubThemeNameID(String mainTheme, String _subTheme) {
            mainThemeID = mainTheme;
            subTheme = _subTheme;
            
            hashCode = mainTheme.hashCode() + subTheme.hashCode();
        }
        
        public void addFirstLine(String fLine, int hymnNumber) {
            firstLines += hymnNumber + fLine + "\n";
        }
        
        @Override
        public boolean equals(Object obj) {
            if(!(obj instanceof SubThemeNameID)) {
               return false;
            }
            if(!((SubThemeNameID)obj).mainThemeID.equals(mainThemeID)) {
                return false;
            }
            if(!((SubThemeNameID)obj).subTheme.equals(subTheme)) {
                return false;
            }
            return true;
        }
        @Override     
        public int hashCode() {
            
            return hashCode;
        }
    }
    
    private final Map<String, SingleHymn> firstCharTable;
    private final Map<ThemeNameID, SingleHymn> mainCategoryTable;
    private final Map<SubThemeNameID, String> subCategoryTable;
    
    void addToSubCategoryTable(SingleHymn hymn) {
        
        SubThemeNameID newSubTheme = new SubThemeNameID(hymn.mainTheme, hymn.subTheme);
        String fLine = subCategoryTable.get(newSubTheme);
        if(fLine == null) {
            subCategoryTable.put(newSubTheme, hymn.numberRu  + ": "+ hymn.firstCoupletLine + "\n");
        } else {
            fLine += hymn.numberRu  + ": "+ hymn.firstCoupletLine + "\n";
            subCategoryTable.put(newSubTheme, fLine);
        }
        
    }
    static public TableManager getInstance(){
        if(instance == null) {
            instance = new TableManager(melodyTableFile);
        }
        return instance;
    }
    
    private TableManager(String melodyTableFileArg) {
        firstCharTable = new HashMap();
        mainCategoryTable = new HashMap<>();
        subCategoryTable = new HashMap();
        melodyTableFile = melodyTableFileArg;
        
    }
    
    public boolean tableManagerInit() {
        openConnection();
        createTables();
        
        return true;
    }

    public void close() {
        try {
            dbConnector.close();
        } catch (SQLException sqe) {

        }
    }
    public void saveFirstChars() {
        Set<String> v = firstCharTable.keySet();
        Object[] obj = v.toArray();
        String sqlinsert = "";
        for(int i = 0; i < obj.length; i++) {
            String s = (String)obj[i];
            sqlinsert = "INSERT INTO " + TableNames.TableFirstCharName + " VALUES(" +
                    "\"" + s + "\"" + 
                    ");\n";            
            executeSQL(sqlinsert);
        }        
    }
    
    public void saveSubThemeCategory() {
        Set<SubThemeNameID> v = subCategoryTable.keySet();
        Object obj[] =  v.toArray();
        for(int i = 0; i < obj.length; i++) {
            SubThemeNameID s = (SubThemeNameID)obj[i];
            String mainCategory = s.mainThemeID;
            String subCategory = s.subTheme;
            String fLines = subCategoryTable.get(s);
            String sqlinsert = "INSERT INTO " + TableNames.TableSubCategoryName + " VALUES(" +
                    "\"" + mainCategory + "\","  +
                    "\"" + subCategory + "\"," + 
                    "\"" + fLines + "\"" + 
                    ");\n";    
            executeSQL(sqlinsert);            
        }
    }
    
    public void saveMainThemeCategory() {
        Set<ThemeNameID> v = mainCategoryTable.keySet();
        
        Object[] obj = v.toArray();
        String sqlinsert = "";
        for(int i = 0; i < obj.length; i++) {
            ThemeNameID s = (ThemeNameID)obj[i];
            sqlinsert = "INSERT INTO " + TableNames.TableMainCategoryName + " VALUES(" +
                    s.themeID + ","  +
                    "\"" + s.themeName + "\"" + 
                    ");\n";            
            executeSQL(sqlinsert);
        }        
    }
    String currentCategory;
    int categoryID = 0;
    public void insertSingleHymn(SingleHymn hymn) {        
        String sqlinsert = "INSERT INTO " + TableNames.TableName + " VALUES("
                + Integer.toString(hymn.numberRu) + ","
                + Integer.toString(hymn.numberRuOld) + ","
                + Integer.toString(hymn.numberEn) + ","
                + "\"" + hymn.firstCoupletLine + "\","
                + "\"" + hymn.firstCoupletChar + "\","
                
                + "\"" + hymn.firstPripevLine + "\","
                + "\"" + hymn.firstPripevChar + "\","
                
                + "\"" + hymn.mainTheme + "\","
                + "\"" + hymn.subTheme + "\","
                + "\"" + hymn.midiFilename + "\","
                + "\"" + hymn.tuneFilename + "\""
                //+ "\"" + hymn.text + "\""
                + ");";
        executeSQL(sqlinsert);
        firstCharTable.put(hymn.firstCoupletChar, hymn);
        if(hymn.firstPripevChar != null) {
            firstCharTable.put(hymn.firstPripevChar, hymn);
        }
        if(hymn.mainTheme != null) {
            ThemeNameID id = null;
            if(currentCategory == null) {
                currentCategory = hymn.mainTheme;
                id = new ThemeNameID(categoryID, hymn.mainTheme);
                mainCategoryTable.put(id, hymn);
                categoryID++;
                
            } else {
                if(!currentCategory.equals(hymn.mainTheme)) {
                    currentCategory = hymn.mainTheme;
                    id = new ThemeNameID(categoryID, hymn.mainTheme);
                    mainCategoryTable.put(id, hymn);
                    categoryID++;
                }
            }
            
            
            
        }
        if(hymn.firstCoupletChar != null) {
            String sqlInsert1 = "INSERT INTO " + TableNames.TableFirstLineName + " VALUES( " +
                    String.valueOf(hymn.numberRu) + "," + 
                    "\"" + hymn.firstCoupletChar + "\"," +
                    "\"" + hymn.firstCoupletLine + "\"" +
                    ");";
            executeSQL(sqlInsert1);
        }
        
        if(hymn.firstPripevChar != null) {
            String sqlInsert2 = "INSERT INTO " + TableNames.TableFirstLineName + " VALUES( " +
                    String.valueOf(hymn.numberRu) + "," + 
                    "\"" + hymn.firstPripevChar + "\"," +
                    "\"" + hymn.firstPripevLine + "\"" +
                    ");";
            executeSQL(sqlInsert2);
        }
    }
    
    
    private void openConnection() {
        try {
            File file = new File("tableHeader.db");

            file.delete();
            
            file = null;
            Class.forName("org.sqlite.JDBC");
            dbConnector = DriverManager.getConnection("jdbc:sqlite:tableHeader.db");
        } catch (Exception e) {
            System.err.println(e.getClass().getName() + ": " + e.getMessage());
            System.exit(0);
        }
        System.out.println("Opened database successfully");        
    }
    
    private void createTables() {
        createMainTable();
        createFirstCharTable();
        createMainCategoryTable();
        createFirstLineTable();
        createSubCategoryTable();
        
    }
    
    private void createMainCategoryTable() {
        String sqlCreateTable = "CREATE TABLE " + TableNames.TableMainCategoryName + " (" +                
                "MAIN_CATEGORY_ID INT NOT NULL, " + 
                "MAIN_CATEGORY varchar(100) NOT NULL" +
        ");";
        executeSQL(sqlCreateTable);
    }
    
     private void createSubCategoryTable() {
        String sqlCreateTable = "CREATE TABLE " + TableNames.TableSubCategoryName + " (" +                
                "MAIN_CATEGORY_ID INT NOT NULL, " + 
                "SUB_CATEGORY varchar(100) NOT NULL, " + 
                "FIRST_LINES TEXT NOT NULL" + 
        ");";
        executeSQL(sqlCreateTable);
    }
    
    private void createFirstCharTable() {
        
        String sqlCreateTable = "CREATE TABLE " + TableNames.TableFirstCharName + " (" +                
                "FIRST_CHAR varchar(1) NOT NULL" +
        ");";
        executeSQL(sqlCreateTable);
        
    }
    
    private void createFirstLineTable() {
        
        String sqlCreateTable = "CREATE TABLE " + TableNames.TableFirstLineName + " (" +                
                "HYMN_NUMBER INT ," + 
                "FIRST_CHAR varchar(1) NOT NULL ," +
                "FIRST_LINE varchar(200)" + 
        ");";
        executeSQL(sqlCreateTable);
        
    }
    
    private void createMainTable() {
        
        String sqlCreateTable = "CREATE TABLE " + TableNames.TableName + " (" +
                "HYMN_NUMBER_RU INT PRIMARY KEY NOT NULL," + 
                "HYMN_NUMBER_RU_OLD INT NOT NULL," + 
                "HYMN_NUMBER_EN INT NOT NULL," + 
                "COUPLET_FIRST_LINE varchar(200) NOT NULL," + 
                "COUPLET_FIRST_CHAR varchar(3) NOT NULL," + 
                "REFRAIN_FIRST_LINE varchar(200) NOT NULL," + 
                "REFRAIN_FIRST_CHAR varchar(3) NOT NULL," + 
                "MAIN_THEME varchar(200) NOT NULL," + 
                "SUB_THEME varchar(200) NOT NULL," + 
                "MIDI_FILE_NAME varchar(20)," + 
                "TUNE_FILE_NAME varchar(20)" + 
                //"HYMN_TEXT TEXT NOT NULL" +
        ");";
        executeSQL(sqlCreateTable);
    }
    
    void executeSQL(String sql) {
        Statement stmt = null;
        try {
            stmt = dbConnector.createStatement();
        } catch (SQLException sqle) {
            System.out.println("sqle:" + sqle.getMessage());
            System.exit(0);
        }
        
        try {
            stmt.execute(sql);            
        } catch (SQLException sqle) {
            System.out.println("sql script:" + sql);
            System.out.println("sqle:" + sqle.getMessage());
            System.exit(0);
        }
        
        try {
            stmt.close();
        } catch (SQLException sqle) {

        }
    }
    


    
}
